package Entidades;

import View.MainScreen;
import View.VerEndereco;
import View.verSolucao;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.swing.JOptionPane;
import javax.swing.table.DefaultTableModel;

public class Os {

    private Connection conn;
    private String query;
    private PreparedStatement stmt;
    private ResultSet rs;

    public void salvarOs(int protocoloOS, int idLogin, String empresaOS,
            String enderecoOs, String contatoOs, String setorOs, String telefoneOS,
            String telefoneOs2, String equipamentoOS, String defeitoOS, String SolucaoOS,
            boolean urgenciaOs, boolean statusOs, Date dataOs) throws SQLException {

        conn = LinkDB.LinkDB.getConexao();
        query = "INSERT INTO os(protocolo, idLogin, empresa,"
                + "endereco, contato, setor, telefone, telefone2, "
                + "equipamento, defeito, solucao, urgencia, status, data) "
                + "VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

        stmt = conn.prepareStatement(query);

        stmt.setInt(1, protocoloOS);
        stmt.setInt(2, idLogin);
        stmt.setString(3, empresaOS);
        stmt.setString(4, enderecoOs);
        stmt.setString(5, contatoOs);
        stmt.setString(6, setorOs);
        stmt.setString(7, telefoneOS);
        stmt.setString(8, telefoneOs2);
        stmt.setString(9, equipamentoOS);
        stmt.setString(10, defeitoOS);
        stmt.setString(11, SolucaoOS);
        stmt.setBoolean(12, urgenciaOs);
        stmt.setBoolean(13, statusOs);
        stmt.setDate(14, dataOs);

        stmt.executeUpdate();
        stmt.close();
        conn.close();

        JOptionPane.showMessageDialog(null, "Chamado salvo com sucesso.");

    }

    public void verChamadosReferencia(String empresa) throws SQLException {
        conn = LinkDB.LinkDB.getConexao();
        query = "SELECT * FROM os WHERE os.empresa "
                + "LIKE CONCAT('%', ?, '%')GROUP BY os.endereco "
                + "ORDER BY os.data DESC";
        stmt = conn.prepareStatement(query);
        stmt.setString(1, empresa);
        rs = stmt.executeQuery();
        DefaultTableModel model = (DefaultTableModel) VerEndereco.ver.getModel();
        while (rs.next()) {
            model.addRow(new Object[]{
                        rs.getString("empresa"),
                        rs.getString("endereco"),
                        rs.getString("contato"),
                        rs.getString("setor"),
                        rs.getString("telefone"),
                        rs.getString("telefone2")
                    });
            VerEndereco.ver.repaint();
        }
        rs.close();
        stmt.close();
        conn.close();
    }
    
    public void carregarTodosEquipamentos() throws SQLException{
        conn = LinkDB.LinkDB.getConexao();
        query = "SELECT os.equipamento "
                + "FROM os "
                + "GROUP BY os.equipamento";
        stmt = conn.prepareStatement(query);        
        rs = stmt.executeQuery();
        while(rs.next()){
            MainScreen.equipamento.addItem(rs.getString("os.equipamento"));
        }
        rs.close();
        stmt.close();
        conn.close();
    }  
    
    public void verSolucoesReferencia(String defeito_ref, String equipamento_ref) 
            throws SQLException {
        conn = LinkDB.LinkDB.getConexao();
        query = "SELECT os.equipamento, os.defeito, os.solucao "
                + "FROM os "
                + "WHERE os.defeito LIKE CONCAT('%', ?, '%') "
                + "AND os.equipamento = ? "
                + "GROUP BY os.solucao "
                + "ORDER BY os.data DESC";
        stmt = conn.prepareStatement(query);
        stmt.setString(1, defeito_ref);
        stmt.setString(2, equipamento_ref);
        rs = stmt.executeQuery();
        DefaultTableModel model = (DefaultTableModel) verSolucao.verSolucaoTable.getModel();
        while (rs.next()) {
            model.addRow(new Object[]{
                        rs.getString("os.equipamento"),
                        rs.getString("os.defeito"),                        
                        rs.getString("os.solucao")
                    });
            verSolucao.verSolucaoTable.repaint();
        }
        rs.close();
        stmt.close();
        conn.close();
    }
    
}
